package com.fastbee.web.wnx.service;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;

import groovy.util.logging.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.*;

@Service
@Slf4j
public class DatabaseMetaService {

    @Autowired
    private ApplicationContext applicationContext;

    private DynamicRoutingDataSource dynamicRoutingDataSource;

    private static final Logger log = LoggerFactory.getLogger(DatabaseMetaService.class);

    @PostConstruct
    public void init() {
        this.dynamicRoutingDataSource = getDynamicDataSource();
        if (this.dynamicRoutingDataSource != null) {
            log.info("动态数据源初始化成功，可用数据源: {}",
                    this.dynamicRoutingDataSource.getDataSources().keySet());
        } else {
            log.error("动态数据源初始化失败");
        }
    }

    /**
     * 获取动态数据源（多种方式尝试）
     */
    private DynamicRoutingDataSource getDynamicDataSource() {
        // 方式1：通过Bean名称获取
        if (applicationContext.containsBean("dynamicRoutingDataSource")) {
            return applicationContext.getBean("dynamicRoutingDataSource", DynamicRoutingDataSource.class);
        }

        // 方式2：通过类型获取
        try {
            return applicationContext.getBean(DynamicRoutingDataSource.class);
        } catch (Exception e) {
            log.warn("通过类型获取 DynamicRoutingDataSource 失败: {}", e.getMessage());
        }

        // 方式3：从 DataSource Bean 中获取
        try {
            DataSource dataSource = applicationContext.getBean(DataSource.class);
            if (dataSource instanceof DynamicRoutingDataSource) {
                return (DynamicRoutingDataSource) dataSource;
            }
        } catch (Exception e) {
            log.warn("从 DataSource Bean 中获取失败: {}", e.getMessage());
        }

        // 方式4：尝试通过其他可能的Bean名称
        String[] beanNames = applicationContext.getBeanNamesForType(DataSource.class);
        for (String beanName : beanNames) {
            try {
                DataSource ds = applicationContext.getBean(beanName, DataSource.class);
                if (ds instanceof DynamicRoutingDataSource) {
                    return (DynamicRoutingDataSource) ds;
                }
            } catch (Exception e) {
                // 忽略异常，继续尝试下一个
            }
        }

        return null;
    }

    /**
     * 添加动态数据源
     */
    public boolean addDynamicDataSource(String dataSourceName,
                                        String url, String username, String password) {
        if (dynamicRoutingDataSource == null) {
            log.error("动态数据源未初始化");
            return false;
        }

        try {
            // 检查数据源是否已存在
            if (dynamicRoutingDataSource.getDataSources().containsKey(dataSourceName)) {
                log.warn("数据源已存在: {}", dataSourceName);
                return true;
            }

            // 创建Druid数据源
            DruidDataSource druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(url);
            druidDataSource.setUsername(username);
            druidDataSource.setPassword(password);
            druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

            // 连接池配置
            druidDataSource.setInitialSize(5);
            druidDataSource.setMinIdle(5);
            druidDataSource.setMaxActive(20);
            druidDataSource.setMaxWait(60000);
            druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
            druidDataSource.setMinEvictableIdleTimeMillis(300000);
            druidDataSource.setValidationQuery("SELECT 1");
            druidDataSource.setTestWhileIdle(true);
            druidDataSource.setTestOnBorrow(false);
            druidDataSource.setTestOnReturn(false);

            // 添加到动态数据源
            dynamicRoutingDataSource.addDataSource(dataSourceName, druidDataSource);

            log.info("动态数据源添加成功: {}", dataSourceName);
            return true;

        } catch (Exception e) {
            log.error("添加动态数据源失败: {}", dataSourceName, e);
            return false;
        }
    }

    /**
     * 移除动态数据源
     */
    public boolean removeDynamicDataSource(String dataSourceName) {
        if (dynamicRoutingDataSource == null) {
            log.error("动态数据源未初始化");
            return false;
        }

        try {
            dynamicRoutingDataSource.removeDataSource(dataSourceName);
            log.info("动态数据源移除成功: {}", dataSourceName);
            return true;
        } catch (Exception e) {
            log.error("移除动态数据源失败: {}", dataSourceName, e);
            return false;
        }
    }

    /**
     * 获取所有数据源名称
     */
    public Set<String> getAllDataSourceNames() {
        if (dynamicRoutingDataSource == null) {
            return Collections.emptySet();
        }
        return dynamicRoutingDataSource.getDataSources().keySet();
    }

    /**
     * 测试数据源连接
     */
    public boolean testDataSourceConnection(String dataSourceName) {
        if (dynamicRoutingDataSource == null) {
            return false;
        }

        try {
            DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
            if (dataSource == null) {
                return false;
            }

            Connection connection = dataSource.getConnection();
            if (connection != null) {
                connection.close();
                return true;
            }
        } catch (Exception e) {
            log.error("测试数据源连接失败: {}", dataSourceName, e);
        }
        return false;
    }

    /**
     * 执行SQL查询
     */
//    public List<Map<String, Object>> executeQuery(String dataSourceName, String sql) {
//        if (dynamicRoutingDataSource == null) {
//            throw new RuntimeException("动态数据源未初始化");
//        }
//
//        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
//        if (dataSource == null) {
//            throw new RuntimeException("数据源不存在: " + dataSourceName);
//        }
//
//        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//        return jdbcTemplate.queryForList(sql);
//    }



    /**
     * 基础查询 - 返回List<Map>
     */
    public List<Map<String, Object>> queryForList(String dataSourceName, String sql, Object... params) {
        if (dynamicRoutingDataSource == null) {
            throw new RuntimeException("动态数据源未初始化");
        }

        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataSourceName);
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            if (params == null || params.length == 0) {
                return jdbcTemplate.queryForList(sql);
            } else {
                return jdbcTemplate.queryForList(sql, params);
            }
        } catch (Exception e) {
            log.error("执行查询失败 - 数据源: {}, SQL: {}", dataSourceName, sql, e);
            throw new RuntimeException("查询失败: " + e.getMessage(), e);
        }
    }

    /**
     * 查询单条记录
     */
    public Map<String, Object> queryForMap(String dataSourceName, String sql, Object... params) {
        List<Map<String, Object>> result = queryForList(dataSourceName, sql, params);
        if (result != null && !result.isEmpty()) {
            return result.get(0);
        }
        return Collections.emptyMap();
    }

    /**
     * 查询单个值
     */
    public <T> T queryForObject(String dataSourceName, String sql, Class<T> requiredType, Object... params) {
        if (dynamicRoutingDataSource == null) {
            throw new RuntimeException("动态数据源未初始化");
        }

        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataSourceName);
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            if (params == null || params.length == 0) {
                return jdbcTemplate.queryForObject(sql, requiredType);
            } else {
                return jdbcTemplate.queryForObject(sql, requiredType, params);
            }
        } catch (Exception e) {
            log.error("查询单个值失败 - 数据源: {}, SQL: {}", dataSourceName, sql, e);
            throw new RuntimeException("查询失败: " + e.getMessage(), e);
        }
    }

    /**
     * 查询对象列表（映射到Java Bean）
     */
    public <T> List<T> queryForBeanList(String dataSourceName, String sql, Class<T> elementType, Object... params) {
        if (dynamicRoutingDataSource == null) {
            throw new RuntimeException("动态数据源未初始化");
        }

        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataSourceName);
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(elementType);
            if (params == null || params.length == 0) {
                return jdbcTemplate.query(sql, rowMapper);
            } else {
                return jdbcTemplate.query(sql, rowMapper, params);
            }
        } catch (Exception e) {
            log.error("查询对象列表失败 - 数据源: {}, SQL: {}", dataSourceName, sql, e);
            throw new RuntimeException("查询失败: " + e.getMessage(), e);
        }
    }

    /**
     * 分页查询
     */
    public Map<String, Object> queryForPage(String dataSourceName, String sql,
                                            int pageNum, int pageSize, Object... params) {
        Map<String, Object> result = new HashMap<>();

        // 计算总数
        String countSql = "SELECT COUNT(*) FROM (" + sql + ") as total_table";
        Long total = queryForObject(dataSourceName, countSql, Long.class, params);

        // 分页查询
        String pageSql = sql + " LIMIT " + pageSize + " OFFSET " + (pageNum - 1) * pageSize;
        List<Map<String, Object>> data = queryForList(dataSourceName, pageSql, params);

        result.put("data", data);
        result.put("total", total);
        result.put("pageNum", pageNum);
        result.put("pageSize", pageSize);
        result.put("totalPages", (total + pageSize - 1) / pageSize);

        return result;
    }

    /**
     * 执行更新操作（INSERT, UPDATE, DELETE）
     */
    public int executeUpdate(String dataSourceName, String sql, Object... params) {
        if (dynamicRoutingDataSource == null) {
            throw new RuntimeException("动态数据源未初始化");
        }

        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataSourceName);
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            log.info("执行更新SQL{}",sql);
            if (params == null || params.length == 0) {
                return jdbcTemplate.update(sql);
            } else {
                return jdbcTemplate.update(sql, params);
            }
        } catch (Exception e) {
            log.error("执行更新失败 - 数据源: {}, SQL: {}", dataSourceName, sql, e);
            throw new RuntimeException("更新失败: " + e.getMessage(), e);
        }
    }

    /**
     * 插入数据并返回自增主键
     */
    public Long insertAndReturnKey(String dataSourceName, String sql, Object... params) {
        if (dynamicRoutingDataSource == null) {
            throw new RuntimeException("动态数据源未初始化");
        }

        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataSourceName);
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        KeyHolder keyHolder = new GeneratedKeyHolder();

        try {
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                if (params != null) {
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, params[i]);
                    }
                }
                return ps;
            }, keyHolder);

            return keyHolder.getKey() != null ? keyHolder.getKey().longValue() : null;
        } catch (Exception e) {
            log.error("插入数据失败 - 数据源: {}, SQL: {}", dataSourceName, sql, e);
            throw new RuntimeException("插入失败: " + e.getMessage(), e);
        }
    }

    /**
     * 批量执行更新操作
     */
    public int[] executeBatchUpdate(String dataSourceName, String sql, List<Object[]> batchArgs) {
        if (dynamicRoutingDataSource == null) {
            throw new RuntimeException("动态数据源未初始化");
        }

        DataSource dataSource = dynamicRoutingDataSource.getDataSource(dataSourceName);
        if (dataSource == null) {
            throw new RuntimeException("数据源不存在: " + dataSourceName);
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            return jdbcTemplate.batchUpdate(sql, batchArgs);
        } catch (Exception e) {
            log.error("批量更新失败 - 数据源: {}, SQL: {}", dataSourceName, sql, e);
            throw new RuntimeException("批量更新失败: " + e.getMessage(), e);
        }
    }

    /**
     * 根据ID单条删除
     */
    public boolean deleteById(String dataSourceName, String tableName, Long id) {
        return deleteById(dataSourceName, tableName, "id", id);
    }

    /**
     * 根据指定字段和值删除
     */
    public boolean deleteById(String dataSourceName, String tableName, String idColumn, Object idValue) {
        try {
            // 安全检查
            if (!isDeleteSafe(tableName, idColumn, idValue)) {
                log.warn("删除操作安全检查未通过: {}.{} = {}", tableName, idColumn, idValue);
                return false;
            }

            String sql = "DELETE FROM " + tableName + " WHERE " + idColumn + " = ?";
            int affectedRows = executeUpdate(dataSourceName, sql, idValue);

            if (affectedRows > 0) {
                log.info("删除成功 - 表: {}, 条件: {} = {}, 影响行数: {}",
                        tableName, idColumn, idValue, affectedRows);
                return true;
            } else {
                log.warn("删除操作未影响任何行 - 表: {}, 条件: {} = {}", tableName, idColumn, idValue);
                return false;
            }

        } catch (Exception e) {
            log.error("删除数据失败 - 表: {}, 条件: {} = {}", tableName, idColumn, idValue, e);
            throw new RuntimeException("删除失败: " + e.getMessage(), e);
        }
    }

    /**
     * 批量删除（根据ID列表）
     */
    public int batchDeleteByIds(String dataSourceName, String tableName, List<Long> ids) {
        return batchDeleteByIds(dataSourceName, tableName, "id", ids);
    }


    /**
     * 批量删除（根据指定字段和值列表）
     */
    public int batchDeleteByAll(String dataSourceName, String tableName) {


        try {
            // 构建IN语句

            String sql = "DELETE FROM " + tableName ;

            int affectedRows =executeUpdate(dataSourceName, sql,null);

            log.info("批量删除成功 - 表: {}, 影响行数: {}", tableName,  affectedRows);
            return affectedRows;

        } catch (Exception e) {
            log.error("批量删除失败 - 表: {}", tableName);
            throw new RuntimeException("批量删除失败: " + e.getMessage(), e);
        }
    }

    /**
     * 批量删除（根据指定字段和值列表）
     */
    public int batchDeleteByIds(String dataSourceName, String tableName, String idColumn, List<?> idValues) {
        if (idValues == null || idValues.isEmpty()) {
            log.warn("批量删除ID列表为空");
            return 0;
        }

        try {
            // 安全检查
            if (!isBatchDeleteSafe(tableName, idColumn, idValues)) {
                log.warn("批量删除操作安全检查未通过: {}.{}", tableName, idColumn);
                return 0;
            }

            // 构建IN语句
            String placeholders = String.join(",", Collections.nCopies(idValues.size(), "?"));
            String sql = "DELETE FROM " + tableName + " WHERE " + idColumn + " IN (" + placeholders + ")";

            int affectedRows =executeUpdate(dataSourceName, sql, idValues.toArray());

            log.info("批量删除成功 - 表: {}, 条件: {} IN ({}个值), 影响行数: {}",
                    tableName, idColumn, idValues.size(), affectedRows);
            return affectedRows;

        } catch (Exception e) {
            log.error("批量删除失败 - 表: {}, 条件: {} IN ({}个值)",
                    tableName, idColumn, idValues.size(), e);
            throw new RuntimeException("批量删除失败: " + e.getMessage(), e);
        }
    }

    /**
     * 安全删除（先查询后删除，记录日志）
     */
    public Map<String, Object> safeDeleteById(String dataSourceName, String tableName, Long id,
                                              String operator, String reason) {
        return safeDeleteById(dataSourceName, tableName, "id", id, operator, reason);
    }

    /**
     * 安全删除（先查询后删除，记录日志）
     */
    public Map<String, Object> safeDeleteById(String dataSourceName, String tableName,
                                              String idColumn, Object idValue,
                                              String operator, String reason) {
        Map<String, Object> result = new HashMap<>();

        try {
            // 1. 先查询要删除的数据
            String selectSql = "SELECT * FROM " + tableName + " WHERE " + idColumn + " = ?";
            List<Map<String, Object>> records = queryForList(dataSourceName, selectSql, idValue);

            if (records.isEmpty()) {
                result.put("success", false);
                result.put("message", "未找到要删除的数据");
                result.put("affectedRows", 0);
                return result;
            }

            Map<String, Object> recordToDelete = records.get(0);

            // 2. 执行删除
            String deleteSql = "DELETE FROM " + tableName + " WHERE " + idColumn + " = ?";
            int affectedRows = executeUpdate(dataSourceName, deleteSql, idValue);

            // 3. 记录删除日志（可选）
            if (affectedRows > 0) {
                logDeleteOperation(tableName, idColumn, idValue, recordToDelete, operator, reason);
            }

            result.put("success", affectedRows > 0);
            result.put("message", affectedRows > 0 ? "删除成功" : "删除失败");
            result.put("affectedRows", affectedRows);
            result.put("deletedRecord", recordToDelete);

        } catch (Exception e) {
            log.error("安全删除失败 - 表: {}, 条件: {} = {}", tableName, idColumn, idValue, e);
            result.put("success", false);
            result.put("message", "删除失败: " + e.getMessage());
            result.put("affectedRows", 0);
        }

        return result;
    }

    /**
     * 条件删除（支持复杂条件）
     */
    public int deleteByCondition(String dataSourceName, String tableName,
                                 Map<String, Object> conditions) {
        if (conditions == null || conditions.isEmpty()) {
            throw new RuntimeException("删除条件不能为空");
        }

        try {
            // 安全检查
//            if (!isConditionDeleteSafe(tableName, conditions)) {
//                log.warn("条件删除安全检查未通过: {}", tableName);
//                return 0;
//            }

            // 构建WHERE条件
            StringBuilder sqlBuilder = new StringBuilder("DELETE FROM ").append(tableName);
            List<Object> params = new ArrayList<>();

            if (!conditions.isEmpty()) {
                sqlBuilder.append(" WHERE ");
                List<String> conditionsList = new ArrayList<>();

                for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                    conditionsList.add(entry.getKey() + " = ?");
                    params.add(entry.getValue());
                }

                sqlBuilder.append(String.join(" AND ", conditionsList));
            }

            int affectedRows = executeUpdate(dataSourceName, sqlBuilder.toString(), params.toArray());

            log.info("条件删除成功 - 表: {}, 条件: {}, 影响行数: {}",
                    tableName, conditions, affectedRows);
            return affectedRows;

        } catch (Exception e) {
            log.error("条件删除失败 - 表: {}, 条件: {}", tableName, conditions, e);
            throw new RuntimeException("条件删除失败: " + e.getMessage(), e);
        }
    }

    /**
     * 软删除（更新状态字段）
     */
    public boolean softDeleteById(String dataSourceName, String tableName, Long id) {
        return softDeleteById(dataSourceName, tableName, "id", id, "status", 0);
    }

    /**
     * 软删除（更新指定状态字段）
     */
    public boolean softDeleteById(String dataSourceName, String tableName,
                                  String idColumn, Object idValue,
                                  String statusColumn, Object statusValue) {
        try {
            String sql = "UPDATE " + tableName + " SET " + statusColumn + " = ? WHERE " + idColumn + " = ?";
            int affectedRows = executeUpdate(dataSourceName, sql, statusValue, idValue);

            if (affectedRows > 0) {
                log.info("软删除成功 - 表: {}, 条件: {} = {}, 设置 {} = {}",
                        tableName, idColumn, idValue, statusColumn, statusValue);
                return true;
            }
            return false;

        } catch (Exception e) {
            log.error("软删除失败 - 表: {}, 条件: {} = {}", tableName, idColumn, idValue, e);
            throw new RuntimeException("软删除失败: " + e.getMessage(), e);
        }
    }

    /**
     * 删除安全检查
     */
    private boolean isDeleteSafe(String tableName, String idColumn, Object idValue) {
        // 检查表名是否合法（防止SQL注入）
        if (!tableName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
            return false;
        }

        // 检查字段名是否合法
        if (!idColumn.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
            return false;
        }

        // 检查ID值是否合法
        if (idValue == null) {
            return false;
        }

        // 可以添加更多的安全检查规则
        // 比如：禁止删除系统表、重要数据表等

        return true;
    }

    /**
     * 批量删除安全检查
     */
    private boolean isBatchDeleteSafe(String tableName, String idColumn, List<?> idValues) {
        if (!isDeleteSafe(tableName, idColumn, "batch")) {
            return false;
        }

        // 检查批量删除数量限制（防止误删大量数据）
        if (idValues.size() > 1000) {
            log.warn("批量删除数量超过限制: {}", idValues.size());
            return false;
        }

        return true;
    }

    /**
     * 条件删除安全检查
     */
    private boolean isConditionDeleteSafe(String tableName, Map<String, Object> conditions) {
        if (!tableName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
            return false;
        }

        for (String column : conditions.keySet()) {
            if (!column.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
                return false;
            }
        }

        return true;
    }

    /**
     * 记录删除日志（可扩展为写入日志表）
     */
    private void logDeleteOperation(String tableName, String idColumn, Object idValue,
                                    Map<String, Object> deletedRecord, String operator, String reason) {
        // 这里可以记录到日志文件、数据库日志表等
        log.info("删除操作记录 - 表: {}, 字段: {}, 值: {}, 操作人: {}, 原因: {}, 删除数据: {}",
                tableName, idColumn, idValue, operator, reason, deletedRecord);
    }
}
